/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.dao;

//~--- non-JDK imports --------------------------------------------------------
import com.myapp.struts.actionform.AccountHeadFormBean;
import com.myapp.struts.beans.AccountHeadBean;
import com.myapp.struts.prepare.AccountHeadPrepareAL;

//~--- JDK imports ------------------------------------------------------------

import java.sql.*;

import java.util.*;
import org.apache.struts.action.ActionForm;

public class AcheadDao {

    AccountHeadPrepareAL ahpl = new AccountHeadPrepareAL();
    ArrayList al, alt;
    boolean b1, b;
    Connection con;    // get the connection object reference
    Connect drv;    // get the driver object reference
    int i, count, count1;
    PreparedStatement pstmt;
    ResultSet rs;
    private String createledger;
    private int j;
    private AccountHeadFormBean form1;
    private String date;

//  method to establish a connection to Database
    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();

        return con;
    }    // end of getconnection()

    public boolean insert(ActionForm form) throws Exception {
        try {

            con = getConnect();
            con.setAutoCommit(false);
            /** Type cast ActionForm variable to FormBean */
            form1 = (AccountHeadFormBean) form;
            System.out.println("INSERT METHOD AT acheadDAO...");

            String ss = "INSERT INTO account_head VALUES(?,?,?,?,?,?,?,?,?,?,?)";

            pstmt = con.prepareStatement(ss);
            System.out.println("psmt" + pstmt);
            System.out.println("conn and stmt is created");
            java.sql.Date myDate = new java.sql.Date(System.currentTimeMillis());
            date = myDate.toString();
            String acheadno = form1.getAccountHeadNumber();
            String acheadname = form1.getAccountHeadName();
            String acheadtype = form1.getAccountHeadType();
            double obalance = form1.getOpeningBalance();
            String schedule = form1.getSchedule();
            String amttype = form1.getBalanceType();
            String desc = form1.getDescription();
            String cby ="admin";
            String cdt = date;

            pstmt.setString(1, acheadno);
            pstmt.setString(2, acheadname);
            pstmt.setString(3, acheadtype);
            pstmt.setDouble(4, obalance);
            pstmt.setDouble(5, obalance);
            pstmt.setString(6, schedule);
            pstmt.setString(7, amttype);
            pstmt.setString(8, desc);
            pstmt.setString(9, cby);
            pstmt.setString(10, cdt);
            pstmt.setString(11, "1");

            // System.out.println(code);
            i = pstmt.executeUpdate();


            pstmt.close();
            if (i == 1) {
                createledger = "insert into ledger values(?,?,?,?,?,?,?,?,?)";
                pstmt = con.prepareStatement(createledger);
                pstmt.setString(1, null);
                pstmt.setString(2, acheadno);
                pstmt.setString(3, acheadname);
                pstmt.setString(4, cdt);
                pstmt.setString(5, amttype);
                pstmt.setString(6, "openingbalance");
                pstmt.setString(7, "OB");
                pstmt.setDouble(8, obalance);
                pstmt.setDouble(9, obalance);
                j = pstmt.executeUpdate();
                if (j > 0) {
                    System.out.println("inside AcheadDao values inserted");
                    con.commit();
                    con.close();
                    pstmt.close();
                    b = true;
                } else {
                    b = false;
                }
            } // end of if
            else {
                System.out.println("values not inserted");
                b = false;
            }        // end of else
        } // end of try
        catch (Exception e) {
            System.out.println("inside AcheadDao exception at insert" + e);

            // throw e;
        } // end of catch
        finally {
            if (con != null) {
                try {
                    if (con != null) {
                        con.close();
                    }
                } // end of try
                catch (Exception e) {
                    System.out.println("exception at con closed" + e);
                }    // end of catch
            }
        }            // end of finally

        return b;
    }    // end of insert method

    public ArrayList list() throws Exception {
        try {
            System.out.println("inside list method at AcheadDao");
            con = getConnect();
            ahpl = new AccountHeadPrepareAL();
            System.out.println(con);

            String query = "select * from account_head where Achead_Status=1 order by Ahead_No";

            pstmt = con.prepareStatement(query);
            System.out.println("pstmt............................................" + pstmt);
            System.out.println("con and stmt is created at get()");
            rs = pstmt.executeQuery();
            System.out.println("RS IS ...................." + rs);
            al = ahpl.prepareArrayList(rs);
            System.out.println("al......... is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("exception at list method");
        }    // end of catch

        return al;
    }    // end of list

    public ArrayList editlist(String acheadno) throws Exception {
        try {
            con = getConnect();
            System.out.println("acheadno is  ..........." + acheadno);

            // System.out.println(con);
            String query = "select * from account_head where Ahead_No ='" + acheadno + "'";

            System.out.println(query);

            // System.out.println("AFTER THE QUERY "+query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            // pstmt.setString(1, acheadno);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            al = ahpl.prepareArrayList(rs);
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }    // end of editlist method

    public ArrayList acheadDetails(String acheadname) throws Exception {
        try {
            con = getConnect();
            System.out.println("acheadno is  ..........." + acheadname);

            // System.out.println(con);
            String query = "select * from account_head where Ahead_Name ='" + acheadname + "'";

            System.out.println("AFTER THE QUERY " + query);
            pstmt = con.prepareStatement(query);

            // System.out.println("values of prepared statement  "+pstmt);
            // pstmt.setString(1, acheadno);
            rs = pstmt.executeQuery();
            System.out.println("RS IS ..................." + rs);
            al = ahpl.prepareArrayList(rs);
            System.out.println("al is not null" + al);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("\n exception at retrieving the data" + e);
        }    // end of catch

        return al;
    }

    public boolean update(AccountHeadBean ahb) throws Exception {
        try {
            con = getConnect();

            con.setAutoCommit(false);

            // System.out.println("con at upadate"+con);
            String sno, sname, fname, sdob, sphoneno, smailid, spreseaddr, spermaaddr, smarks, scategory;

            System.out.println("at  Update method in Achead DAO");

            String ss =
                    "update account_head set Ahead_Name=?,Opening_Balance=?,Schedule=?,Description=?,Amt_Type=?,CreatedBy=?,CreatedDt=? where Ahead_No=?";
            pstmt = con.prepareStatement(ss);

            System.out.println("pstmt at update method" + pstmt);

            // System.out.println("conn and pstmt is created");
            String acheadno = ahb.getAhead_No();
            String acheadname = ahb.getAhead_Name();
            String obalance = ahb.getOpening_Balance();
            String schedule = ahb.getSchedule();
            String amttype = ahb.getAmt_Type();
            String desc = ahb.getDescription();
            String cby = ahb.getCreatedBy();
            String cdt = ahb.getCreatedDt();

            pstmt.setString(1, acheadname);
            pstmt.setString(2, obalance);
            pstmt.setString(3, schedule);
            pstmt.setString(4, amttype);
            pstmt.setString(5, desc);
            pstmt.setString(6, cby);
            pstmt.setString(7, cdt);
            pstmt.setString(8, acheadno);

            // System.out.println("BEFORE EXECUTEUPDATE");
            i = pstmt.executeUpdate();

            con.commit();
            pstmt.close();
            con.close();

            // System.out.println("i value is ......................................."+i);
            if (i == 1) {

                // System.out.println("values are updated......................");;
                b1 = true;
            } // end of if
            else {

                // System.out.println("values are not updated....................");
                b1 = false;
            }    // end of else                                           // con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("INSIDE AcheadDao EXCEPTION AT UPDATE" + e);
        } // end of catch
        finally {
            try {
                if (con != null) {
                    con.close();
                }
            } // end of try
            catch (Exception e) {
                System.out.println("EXCEPTION AT CON CLOSED" + e);
            }    // end of catch
        }        // end of finally

        return b1;
    }    // end of update method

    public int delete(String acheadno) throws Exception {
        try {
            System.out.println("AccountHeadNumber is  ..........." + acheadno);
            con = getConnect();
            con.setAutoCommit(false);

            String query = "update account_head set Achead_Status=0 where Ahead_No=?";
            pstmt = con.prepareStatement(query);

            // System.out.println("pstmt..............................."+pstmt);
            // System.out.println("city code is(ccode) ..........." +ccode);
            pstmt.setString(1, acheadno);
            count = pstmt.executeUpdate();

            // System.out.println("COUNT IS ......"+count);
            con.commit();
            pstmt.close();
            con.close();            

            // System.out.println("inside delete()    \n");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("exception at delete");
        } // end of catch
        finally {
            try {
                if (con != null) {
                    con.close();
                }
            } // end of try
            catch (Exception e) {
            }    // end of catch
        }                                     // end of finally

        return this.count;
    }    // end of delete method

    /*...........................................max value method.................................................................*/
    public String maxValue() throws Exception {
        int p = 0;
        String k = "";

        try {
            con = getConnect();

            String query = "select count(*) from account_head ";

            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();

//          System.out.println("Rs  val"+rs);
            if (rs.next()) {
                p = rs.getInt(1) + 1;

//              System.out.println("p VALUE IS ....................."+p);
            }    // end of if

            k = new Integer(p).toString();

//          System.out.println("k VALUE IS .............................."+k);
            rs.close();
            pstmt.close();
            con.close();
        } // end of try
        catch (Exception e) {
            System.out.println("Exception at MAXmethod" + e);
        }    // end of catch

        return k;
    }

    public ArrayList getCash() throws Exception {
        try {
            System.out.println("inside list method at getCash");
            con = getConnect();

            String query = "SELECT Opening_Balance FROM account_head where Schedule='cash'";

            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            al = ahpl.cashBalance(rs);
            System.out.println("al=" + al);
        } // end of try
        catch (Exception e) {
            System.out.println("Exception at List Method");
            e.printStackTrace();
        }    // end of catch

        return al;
    }    // end of list

    public ArrayList cashAndBankHeads() throws Exception {
        try {
            System.out.println("inside list method at Cash And Bank");
            con = getConnect();

            String query =
                    "SELECT distinct(ahead_name) FROM account_head where Ahead_Type='cash' or Ahead_Type='bank';";

            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            al = ahpl.aheadName(rs);
        } // end of try
        catch (Exception e) {
            System.out.println("Exception at List Method");
            e.printStackTrace();
        }    // end of catch

        return al;
    }

    public ArrayList getHeads() throws Exception {
        try {
            System.out.println("inside list method at Cash And Bank");
            con = getConnect();

            String query = "SELECT distinct(ahead_name) FROM account_head;";

            pstmt = con.prepareStatement(query);
            rs = pstmt.executeQuery();
            al = ahpl.aheadName(rs);
        } // end of try
        catch (Exception e) {
            System.out.println("Exception at List Method");
            e.printStackTrace();
        }    // end of catch

        return al;
    }
}
